package com.mycompany.app.hr.tna.service;

import java.util.Date;

public interface IShiftPatternCalendarService {
    
    
    
    /**
     * procedure sp_get_fiscal_week (
           ip_gid  in attendance_record.gid %type,
           ip_effective_day    in attendance_record.effective_day %type,
           op_from_date        out date,
           op_thru_date        out date
  )
  as
    SHIFT_PATTERN_NOT_FOUND exception;

    t_from_date date;
    t_thru_date date;
  begin
      for idx in (
         select distinct s.shift_date as from_date, e.shift_date as thru_date
         from (
               select distinct spd.fiscal_week_num,spd.shift_date from shift_pattern_day spd
               where spd.fiscal_week_day = 1
              ) s,
              (
               select distinct spd.fiscal_week_num,spd.shift_date from shift_pattern_day spd
               where spd.fiscal_week_day = 7
              ) e
          where e.shift_date - s.shift_date + 1 = 7
                and s.fiscal_week_num = e.fiscal_week_num
                and ip_effective_day between s.shift_date and e.shift_date
      ) loop
          t_from_date := idx.from_date;
          t_thru_date := idx.thru_date;
      end loop;

      for idx in (
          select
              case when e.latest_hire_date between t_from_date and t_thru_date then
                 e.latest_hire_date
              else
                 t_from_date
              end from_date,
              case when e.termination_date between t_from_date and t_thru_date then
                 e.termination_date
              else
                 t_thru_date
              end thru_date
          from employee e
          where e.gid = ip_gid
      ) loop
          op_from_date := idx.from_date;
          op_thru_date := idx.thru_date;

          return;
      end loop;

      raise SHIFT_PATTERN_NOT_FOUND;
  end;
     * @param current
     * @return
     */
    public Date getFiscalWeekFromDate(Date current);
    
    public Date getFiscalWeekToDate(Date current);
    
    

}
